BigQuery で実行できる SQL と実行できない SQL

#Google BigQuery

#Google Cloud (GCP)

みかみ

2020.07.31

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

やりたいこと

BigQuery では他のデータベースサービスと同等の SQL 構文がサポートされているのか確認したい
BigQuery でサポートされていない SQL と同等の処理を行うにはどうすればよいか知りたい

前提

BigQuery の標準 SQL を対象とします。

また、SQL の検証には GCP 管理コンソール、bq コマンドまたは Python クライアントライブラリを使用しています。 bq コマンド、Python クライアントライブラリは、CLOUD SHELL から実行しました。

CREATE TABLE

BigQuery でも CREATE TABLE 構文でテーブル作成が可能です。

カラムのデータ型には BigQuery のデータ型を指定する必要があり、他のデータベースサービスでサポートしている VARCHAR や BIGINT などの型は指定できませんが、NOT NULL 指定や OPTIONS 項目でカラムの論理名も付与することができます。

以下の SQL で、テーブルを作成してみます。

CREATE TABLE IF NOT EXISTS dataset_1.test_create (
    id INT64 OPTIONS(description="ID"),
    value STRING NOT NULL OPTIONS(description="カラムの説明")
)

定義した通り、テーブルが無事作成できました。

テーブルに対して OPTIONS 指定することで、パーティショニングや有効期限も指定できます。

以下の SQL で、有効期限とテーブルの説明を付与して既存のテーブルを置き換えてみます。

CREATE OR REPLACE TABLE dataset_1.test_create (
    id INT64 OPTIONS(description="ID"),
    value STRING NOT NULL OPTIONS(description="カラムの説明")
)
OPTIONS(
    expiration_timestamp=TIMESTAMP "2020-12-31 23:59:59 Asia/Tokyo",
    friendly_name="テーブルの分かりやすい名前",
    description="テーブルの説明"
)

期待通り、OPTIONS が反映されたことが確認できました。

CTAS

CREATE TABLE ... AS もサポートしているので、他テーブルからの SELECT 結果で新しいテーブルを作成することもできます。

CREATE OR REPLACE TABLE dataset_1.test_create
AS SELECT * FROM dataset_1.table_sample

カラムの NOT NULL 制約や description、テーブルの有効期限などのオプションは引き継がれませんが、元テーブルと同じテーブル定義、同じデータを持つテーブルが作成できました。

CREATE TABLE LIKE

BigQuery は CREATE TABLE ... LIKE 構文はサポートしていません。

ですが、前述の CREATE TABLE ... AS を使って SELECT 結果が 0 件の SQL を実行すれば、カラム定義が同じ空テーブルが作成されることが確認できました。

TEMPテーブル

BigQuery Scripting を使えば、Temporary テーブルも使用できます。

一時テーブルなので、スクリプトを使わないで作成しようとするとエラーになります。

以下の BigQuery スクリプトで、一時テーブルを作成して結果を SELECT してみます。

CREATE OR REPLACE TEMP TABLE test_create_temp AS SELECT * FROM dataset_1.table_sample;
SELECT name, gender FROM test_create_temp ORDER BY count DESC LIMIT 3;

複数の SQL をセミコロンで区切りさえすれば、bq コマンド経由でももちろん実行できます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'CREATE OR REPLACE TEMP TABLE test_create_temp AS SELECT * FROM dataset_1.table_sample;SELECT name, gender FROM test_create_temp ORDER BY count DESC LIMIT 3;'
Waiting on bqjob_r54c5ca2c915d80b1_0000017395334b5b_1 ... (3s) Current status: DONE   
CREATE OR REPLACE TEMP TABLE test_create_temp AS SELECT * FROM dataset_1.table_sample; -- at [1:1]
Created cm-da-mikami-yuki-258308._script92e81cfeb4fd560670ff07cbbd57ba35884da6b3.test_create_temp

SELECT name, gender FROM test_create_temp ORDER BY count DESC LIMIT 3; -- at [1:87]
+----------+--------+
|   name   | gender |
+----------+--------+
| Isabella | F      |
| Jacob    | M      |
| Sophia   | F      |
+----------+--------+

Python クライアントライブラリ経由の以下のコードでも、Temporary テーブルが利用できることが確認できました。

from google.cloud import bigquery

query = 'CREATE OR REPLACE TEMP TABLE test_create_temp AS SELECT * FROM dataset_1.table_sample;SELECT name, gender FROM test_create_temp ORDER BY count DESC LIMIT 3;'

client = bigquery.Client()
job = client.query(query)
ret = job.result()
for row in ret:
    print('{}: {}'.format(row.name, row.gender))

gcp_da_user@cloudshell:~/temp (cm-da-mikami-yuki-258308)$ python3 create_temptable.py
Isabella: F
Jacob: M
Sophia: F

ALTER TABLE

BigQuey でも ALTER TABLE 構文は使えますが、ラベルや有効期限などのテーブルオプションの変更しかできません。

ALTER TABLE SET OPTIONS ステートメント｜ BigQuery ドキュメント

テーブル定義やテーブルなどを変更したい場合は、別途コンソールや bq update コマンド、テーブルのコピーなどで対応する必要があります。

SELECT

SELECT 文は他のデータベースサービスとそれほど相違なく利用することができます。サブクエリや JOIN も使えますし、GROUP BY や ORDER BY はもちろん、可読性を上げるためにも便利な WITH 句や、グルーピングデータを扱う時によく使う HAVING 句も使用できます。

以下、WITH 句を使った SQL を実行して、期待通りの結果が取得できました。

WITH top AS (
  SELECT
    max(count) AS count
  FROM
    dataset_1.table_sample
  WHERE
    gender = 'F'
)
SELECT
  org.name,
  org.count
FROM
  dataset_1.table_sample as org, top
WHERE
  org.count = top.count

EXCEPT

他のデータベースサービスで 2 つのテーブルの差集合を取得するときに EXCEPT を使うことがあるかと思いますが、BigQuery では SELECT 句で EXCEPT を指定することで 1 つのテーブルから特定のカラムを除いた結果を取得できます。

例えば、ウインドウ関数 ROW_NUMBER で重複レコードをパーティショニングしてユニークなレコードを取得したい場合、他の多くのデータべースサービスでは SELECT 句に row_number を除くテーブルカラムを全て列挙する必要がありますが、BigQuery では EXCEPT を使って SELECT * EXCEPT(row_num) で記述することができます。

以下、サンプル SQL です。 col_1 から col_10 まで、10 個のカラムを持つ table_a から、col_1 がユニークかつ col_10 の昇順でユニークレコードを抽出する場合、他のデータベースサービスでは row_number 以外の col_1 から col_10 まで全てのカラムを SELECT する以下の SQL を書く必要があります。

SELECT
  col_1,
  col_2,
  col_3,
  col_4,
  col_5,
  col_6,
  col_7,
  col_8,
  col_9,
  col_10
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY
        col_1
      ORDER BY
        col_10
    ) as row_number
  FROM
    table_a
) a 
WHERE
  row_num = 1

BigQuery では EXCEPT を使って以下のように書くことができます。

SELECT
  *,
  EXCEPT(row_num)
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY
        col_1
      ORDER BY
        col_10
    ) as row_number
  FROM
    table_a
) a 
WHERE
  row_num = 1

カラムが少ないテーブルであればそれほど問題はないかと思いますが、カラム数が多いテーブルの場合は EXCEPT で row_number を除外できると非常に便利です！

SELECT COUNT(*)

BigQuery はクエリ課金のため、実行したクエリのデータ処理量に従って課金が発生しますが、SELCECT COUNT(*) には料金がかかりません。

34073 行のレコードがあるテーブルに対して SELCECT COUNT(*) クエリを実行した場合の処理データ量を bq query コマンドの --dry_run オプションで確認してみます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> --dry_run \
> 'SELECT COUNT(*) FROM dataset_1.table_sample'
Query successfully validated. Assuming the tables are not modified, running this query will process upper bound of 0 bytes of data.

running this query will process upper bound of 0 bytes of data とのことで、スキャンされるデータ量は 0 byte であることが確認できました。

UNNEST

BigQuery では、構造化データや配列型のカラムを持つテーブルを定義できます。

JOSN データをロードする場合などに便利ですが、一方、ロード済みデータを SELECT する時には 1 レコードに複数のネストデータが存在する、若干扱いにくいデータ構造になってしまいます。

SELECT
  favoriteFruit,
  friends,
  tags
FROM
  `cm-da-mikami-yuki-258308.dataset_2.load_json`
WHERE
  favoriteFruit = 'banana'

UNNEST を使えば、構造化データを平坦化して組み合わせパターン分のレコード数で取得することができます。

SELECT
  favoriteFruit,
  friends,
  tags
FROM
  `cm-da-mikami-yuki-258308.dataset_2.load_json`,
  UNNEST(friends) AS friends,
  UNNEST(tags) AS tags
WHERE
  favoriteFruit = 'banana'

UPDATE/DELETE

BigQuery でも UPDATE や DELETE 構文をサポートしていますが、必ず WHERE 句を指定する必要があります。

テーブルの全てのレコードを更新/削除したい場合、他のデータベースサービスでは WHERE 句なしで SQL を実行できますが、BigQuery ではエラーとなってしまいます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'DELETE FROM dataset_1.test_create'
Error in query string: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r5ea6d98016cdcd7f_00000173a04993b6_1': DELETE must have a WHERE clause at [1:1]

BigQuery でテーブルの全件を UPDATE または DELETE したい場合には、WHERE True を付与する必要があります。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'DELETE FROM dataset_1.test_create WHERE True'
Waiting on bqjob_rc2572d4a97f0417_00000173a04c1dc0_1 ... (0s) Current status: DONE   
Number of affected rows: 34073
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.test_create'
Waiting on bqjob_r621ef1d6564b54ee_00000173a04ceed6_1 ... (0s) Current status: DONE   
+-----+
| f0_ |
+-----+
|   0 |
+-----+

なお、True を意図して WHERE 1 と記述してみたのですが、True とは認識してくれませんでした。。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'DELETE FROM dataset_1.test_create WHERE 1'
Error in query string: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r786bd401ea4e1efb_00000173a04b8b02_1': WHERE clause should return type BOOL, but returns INT64 at [1:41]

TRUNCATE

BigQuery では、TRUNCATE 構文をサポートしていません。

テーブルの全データを削除する場合には、前述の通り WHERE True 付きの DELETE 構文を実行することで TRUNCATE 同等の結果が実現できます。

または、バッチ処理などで連携ファイルや別テーブルから全件洗い替えでデータをロードしたい場合、実行オプションを指定することで、TRUNCATE 不要で処理が実現できます。

例えば GCS に配置された連携ファイルから全件洗い替えでデータをロードする場合には、以下のコマンドで実現できます。

bq load \
    --autodetect \
    --replace \
    --source_format=CSV \
    dataset_1.table_load_sample \
    gs://test-mikami/data_sample.csv

テーブルデータを以下のファイルデータで洗い替えてみます。

col_1, col_2
1,value1
2,value2
3,value3

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_load_sample'
Waiting on bqjob_r521d7be2697ba7e8_00000173a30189eb_1 ... (0s) Current status: DONE   
+-----+
| f0_ |
+-----+
|   5 |
+-----+
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq load \
>     --autodetect \
>     --replace \
>     --source_format=CSV \
>     dataset_1.table_load_sample \
>     gs://test-mikami/data_sample.csv
Waiting on bqjob_r376b17627e7a7292_00000173a301a06d_1 ... (1s) Current status: DONE   
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_load_sample'
Waiting on bqjob_r2af3719e9fbbecca_00000173a301bf24_1 ... (0s) Current status: DONE   
+-----+
| f0_ |
+-----+
|   3 |
+-----+

また、別テーブルから抽出したデータでテーブルデータを洗い替えする場合には、以下のコマンドで実現できます。

bq query \
--destination_table dataset_1.replae_sample \
--replace \
--use_legacy_sql=false \
'SELECT
   name,
   gender,
   count
 FROM
   dataset_1.table_sample
 WHERE
   gender = "F"'

replae_sample テーブルデータを、table_sample から SELECT したデータで書き換えてみます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.replae_sample'
Waiting on bqjob_r6d7bfdab493c81f7_00000173a30c9c19_1 ... (0s) Current status: DONE   
+-------+
|  f0_  |
+-------+
| 34073 |
+-------+
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_sample WHERE gender = "F"'
Waiting on bqjob_r7d28d14f4413436a_00000173a30cbcfb_1 ... (0s) Current status: DONE   
+-------+
|  f0_  |
+-------+
| 19815 |
+-------+
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query \
> --destination_table dataset_1.replae_sample \
> --replace \
> --use_legacy_sql=false \
> 'SELECT
>    name,
>    gender,
>    count
>  FROM
>    dataset_1.table_sample
>  WHERE
>    gender = "F"'
Waiting on bqjob_r3acb17d66e5f10a2_00000173a30d2082_1 ... (0s) Current status: DONE   
+-----------+--------+-------+
|   name    | gender | count |
+-----------+--------+-------+
| Leona     | F      |   256 |
| Donna     | F      |   256 |
| Ariah     | F      |   256 |
（省略）
| Olea      | F      |     5 |
+-----------+--------+-------+
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.replae_sample'
Waiting on bqjob_r12cbfab7bf2e131_00000173a30d7587_1 ... (0s) Current status: DONE   
+-------+
|  f0_  |
+-------+
| 19815 |
+-------+

まとめ（所感）

データ型は BigQuery 特有なものも多いので、確認しておくと良いと思います。

標準 SQL データ型｜ BigQuery ドキュメント

また、BigQuery はクエリ課金なので SQL 実行には注意が必要ですし、トランザクション処理をサポートしていないのでエラー発生時にロールバックが必要な処理の場合は実装を検討する必要があります。

BigQuery では本当にトランザクション処理がサポートされていないのか確認してみた｜ Developers.IO

BigQuery サービス開始当時は DDL や DML をサポートしておらず SQL もレガシー SQL のみだったのでとっつきにくかったと思いますが、標準 SQL のサポートや DML の回数制限の解除などにより、現在では他のデータベースサービスとほとんど同じ使用感で利用できるのではないかと思います。

各 SQL 構文のサポート有無はどんどん更新されていますし、今はサポートされていない SQL 構文も今後サポートされるようになる可能性もあるのではないかと思います。